"""
Case Type   : nls_lower
Case Name   : nls_lower与自定义函数、导出导入结合使用
Create At   : 2025/6/24
Owner       : @m0_68540079
Description :
    1、准备数据
    2、创建函数，与nls_lower结合使用
    3、执行函数
    4、导出
    5、删除函数
    6、导入步骤4导出的数据
    7、执行函数
    8、清理环境
Expect      :
    1、成功
    2、成功
    3、成功
    4、成功
    5、成功
    6、成功
    7、与导出前一致
    8、成功
History     :
"""

import os
import unittest

from yat.test import Node
from yat.test import macro

from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger


class Nls_LowerTest(unittest.TestCase):
    def setUp(self):
        self.log = Logger()
        self.log.info(f'-----{os.path.basename(__file__)} start-----')
        self.pri_sh = CommonSH('PrimaryDbUser')
        self.pri_user = Node('PrimaryDbUser')
        self.constant = Constant()
        self.common = Common()
        self.tb_name = 't_nlslower_0028'
        self.func_name = 'func_nlslower_0028'
        self.dump_path = os.path.join(os.path.dirname(
            macro.DB_INSTANCE_PATH), 'f_nlslower_0028')

    def test_nls_lower(self):
        text = '-----step1:准备数据;expect:成功-----'
        self.log.info(text)
        sql = f'''drop table if exists {self.tb_name};
        create table {self.tb_name}(v1 varchar(100), v2 varchar(100));
        insert into {self.tb_name} values ('АаБбВвГгДдЕе', 'АаБбВвГгДдЕе');
        insert into {self.tb_name} values ('АаБбВвГгДдЕе', 'АаБбВвГгДдЕе');
        insert into {self.tb_name} values ('АаБбВвГгДдЕе', 'АаБбВвГгДдЕе');
        insert into {self.tb_name} values ('АаБбВвГгДдЕе', 'АаБбВвГгДдЕе');
        '''
        sql_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(sql_res)
        self.assertIn(self.constant.TABLE_DROP_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.TABLE_CREATE_SUCCESS, sql_res,
                      f'执行失败：{text}')
        self.assertIn('INSERT 0 1', sql_res, f'执行失败：{text}')
        self.assertEqual(sql_res.count('INSERT 0 1'), 4, f'执行失败：{text}')

        text = '-----step2:创建函数，与is [not] nan结合使用;expect:成功-----'
        self.log.info(text)
        query_sql = f'''drop function if exists {self.func_name};
        create or replace function {self.func_name}()
        returns table(v1 varchar(100), v2 varchar(100)) as \$\$
        declare
        begin
            return query execute 'select nls_lower(v1)::varchar(100), nls_lower(v2)::varchar(100) 
            from {self.tb_name}';
        end;
        \$\$ language plpgsql;
        '''
        res = self.pri_sh.execut_db_sql(query_sql)
        self.log.info(res)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.CREATE_FUNCTION_SUCCESS_MSG, res,
                      f'执行失败：{text}')

        text = '-----step3:执行函数;expect:成功-----'
        self.log.info(text)
        before_dump = self.pri_sh.execut_db_sql(f'select {self.func_name}();')
        self.log.info(before_dump)
        self.assertIn('4 rows', before_dump, f'执行失败：{text}')

        text = '-----step4:导出;expect:成功-----'
        self.log.info(text)
        dump_cmd = f"source {macro.DB_ENV_PATH};" \
                   f"gs_dump " \
                   f"-p {self.pri_user.db_port} " \
                   f"{self.pri_user.db_name} -F c " \
                   f"-f {self.dump_path}"
        self.log.info(dump_cmd)
        dump_msg = self.pri_user.sh(dump_cmd).result()
        self.log.info(dump_msg)
        self.assertIn(self.constant.GS_DUMP_SUCCESS_MSG, dump_msg,
                      '执行失败:' + text)

        text = '-----step5:删除表、删除函数;expect:成功-----'
        self.log.info(text)
        sql = f'drop table if exists {self.tb_name} cascade;' \
              f'drop function if exists {self.func_name};'
        drop_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(drop_res)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, drop_res,
                      f'执行失败：{text}')

        text = '-----step6:导入步骤4导出的数据;expect:成功-----'
        self.log.info(text)
        cmd = f"source {macro.DB_ENV_PATH}; " \
              f"gs_restore " \
              f"-p {self.pri_user.db_port} " \
              f"-d {self.pri_user.db_name} " \
              f"{self.dump_path}"
        self.log.info(cmd)
        res = self.pri_user.sh(cmd).result()
        self.log.info(res)
        self.assertIn(self.constant.RESTORE_SUCCESS_MSG, res,
                      f'执行失败：{text}')

        text = '-----step7: 执行函数;expect:与导出前一致-----'
        self.log.info(text)
        after_restore = self.pri_sh.execut_db_sql(f'select {self.func_name}();')
        self.log.info(after_restore)
        self.assertIn('4 rows', after_restore, f'执行失败：{text}')
        self.assertEqual(before_dump, after_restore, f'执行失败：{text}')

    def tearDown(self):
        text = '-----step8:清理环境; expect:成功-----'
        self.log.info(text)
        cmd = f'rm -rf {self.dump_path};' \
              f'if [ -d {self.dump_path} ]; ' \
              f'then echo "exists"; else echo "not exists"; fi'
        rm_res = self.common.get_sh_result(self.pri_user, cmd)
        self.log.info(rm_res)

        sql = f'drop table if exists {self.tb_name} cascade;'
        drop_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(drop_res)

        sql = f'drop function if exists {self.func_name};'
        drop_func_res = self.pri_sh.execut_db_sql(sql)
        self.log.info(drop_func_res)

        self.assertEqual('not exists', rm_res, '文件删除失败')
        self.assertIn(self.constant.DROP_TABLE_SUCCESS, drop_res,
                      f'执行失败：{text}')
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG, drop_func_res,
                      f'执行失败：{text}')
        self.log.info(f'-----{os.path.basename(__file__)} end-----')
